
package com.zoo.service

import groovy.sql.Sql
import javax.naming.InitialContext

class Database {

    def sql

    def Database() {

        def ds = new InitialContext().lookup("jdbc/mysql")
        sql = new Sql(ds)
    }

    def final static CUSTOMER =
//        """SELECT SQL_CALC_FOUND_ROWS CAST(
        """SELECT IFNULL(CAST(
        CONCAT(
        '{id:',customer_id,
        ',email:',QUOTE(email),
        '}') AS CHAR),'{}') json
        FROM sklad.customer
        ORDER BY customer_id"""

    def selectCustomer(start, limit) {

        def time = System.currentTimeMillis(),
        count = 0, data = []

        sql.eachRow(CUSTOMER, start, limit) {
            data << it.json
        }

        //count = sql.firstRow("SELECT FOUND_ROWS() cnt").cnt
        count = sql.firstRow("SELECT COUNT(*) cnt FROM sklad.customer").cnt
        sql.close()

        return "{success:true,total:${count},time:${System.currentTimeMillis() - time},data:[${data.join(',')}]}"
    }

    def getEpyVendors(start, limit) {

        def time = System.currentTimeMillis(),
            data = []

        sql.eachRow(
            """SELECT IFNULL(CAST(CONCAT(
              '{vid:',sv.vendor_id,
              ',als:',QUOTE(sv.alias),
              ',name:',QUOTE(sv.name),
              ',cnt:',COUNT(it.vendor_id),

              ',amazonMargin:',IFNULL(av.amazonMargin,0),
              ',amazonCharge:',IFNULL(av.amazonCharge,0),
              ',amazonQty:',IFNULL(av.amazonQty,0),
              ',amazonSendQty:',IFNULL(av.amazonSendQty,0),

              ',amzcompMargin:',IFNULL(av.amzcompMargin,0),
              ',amzcompCharge:',IFNULL(av.amzcompCharge,0),
              ',amzcompQty:',IFNULL(av.amzcompQty,0),
              ',amzcompSendQty:',IFNULL(av.amzcompSendQty,0),

              ',ebayMargin:',IFNULL(av.ebayMargin,0),
              ',ebayCharge:',IFNULL(av.ebayCharge,0),
              ',ebayQty:',IFNULL(av.ebayQty,0),
              ',ebaySendQty:',IFNULL(av.ebaySendQty,0),

              ',ebayAliMargin:',IFNULL(av.ebayAliMargin,0),
              ',ebayAliCharge:',IFNULL(av.ebayAliCharge,0),
              ',ebayAliQty:',IFNULL(av.ebayAliQty,0),
              ',ebayAliSendQty:',IFNULL(av.ebayAliSendQty,0),

              ',buycomMargin:',IFNULL(av.buycomMargin,0),
              ',buycomCharge:',IFNULL(av.buycomCharge,0),
              ',buycomQty:',IFNULL(av.buycomQty,0),
              ',buycomSendQty:',IFNULL(av.buycomSendQty,0),

              ',active:',IFNULL(av.active,0),
            '}') AS CHAR),'{}') json
            FROM sklad.itemVendor sv
            LEFT JOIN advert.epyVendors av USING (vendor_id)
            LEFT JOIN sklad.items it USING (vendor_id)
            GROUP BY it.vendor_id""", start, limit)
        { row ->
            data << row.json
        }
        sql.close()

        return "{success:true,total:${count},time:${System.currentTimeMillis() - time},data:[${data.join(',')}]}"
    }

/**********USPS***************/

    def final static UPS_RATE =
        """SELECT SQL_CALC_FOUND_ROWS IFNULL(CAST(
        CONCAT(
        '{id:',id,
        ',weight:',QUOTE(weight),
        ',z2:',IFNULL(z2,0),',z3:',IFNULL(z3,0),',z4:',IFNULL(z4,0),',z5:',IFNULL(z5,0),',z6:',IFNULL(z6,0),',z7:',IFNULL(z7,0),',z8:',IFNULL(z8,0),
        ',z302:',IFNULL(z302,0),',z303:',IFNULL(z303,0),',z304:',IFNULL(z304,0),',z305:',IFNULL(z305,0),',z306:',IFNULL(z306,0),',z307:',IFNULL(z307,0),',z308:',IFNULL(z308,0),
        ',z202:',IFNULL(z202,0),',z203:',IFNULL(z203,0),',z204:',IFNULL(z204,0),',z205:',IFNULL(z205,0),',z206:',IFNULL(z206,0),',z207:',IFNULL(z207,0),',z208:',IFNULL(z208,0),
        ',z224:',IFNULL(z224,0),',z225:',IFNULL(z225,0),',z226:',IFNULL(z226,0),
        ',z242:',IFNULL(z242,0),',z243:',IFNULL(z243,0),',z244:',IFNULL(z244,0),',z245:',IFNULL(z245,0),',z246:',IFNULL(z246,0),',z247:',IFNULL(z247,0),',z248:',IFNULL(z248,0),
        ',z102:',IFNULL(z102,0),',z103:',IFNULL(z103,0),',z104:',IFNULL(z104,0),',z105:',IFNULL(z105,0),',z106:',IFNULL(z106,0),',z107:',IFNULL(z107,0),',z108:',IFNULL(z108,0),
        ',z124:',IFNULL(z124,0),',z125:',IFNULL(z125,0),',z126:',IFNULL(z126,0),
        ',z132:',IFNULL(z132,0),',z133:',IFNULL(z133,0),',z134:',IFNULL(z134,0),',z135:',IFNULL(z135,0),',z136:',IFNULL(z136,0),',z137:',IFNULL(z137,0),',z138:',IFNULL(z138,0),
        ',z102_am:',IFNULL(z102_am,0),',z103_am:',IFNULL(z103_am,0),',z104_am:',IFNULL(z104_am,0),',z105_am:',IFNULL(z105_am,0),',z106_am:',IFNULL(z106_am,0),',z107_am:',IFNULL(z107_am,0),',z108_am:',IFNULL(z108_am,0),
        ',z124_am:',IFNULL(z124_am,0),
        '}') AS CHAR),'{}') json
        FROM sklad.upsrate"""

    def selectUpsRate(start, limit) {

        def time = System.currentTimeMillis(),
        count = 0, data = []

        sql.eachRow(UPS_RATE, start, limit) {
            data << it.json
        }

        count = sql.firstRow("SELECT FOUND_ROWS() cnt").cnt
        sql.close()

        return "{success:true,total:${count},time:${System.currentTimeMillis() - time},data:[${data.join(',')}]}"
    }

    def final static UPS_RATE_UPDATE =
        """UPDATE LOW_PRIORITY sklad.upsrate SET
        z2=:z2,z3=:z3,z4=:z4,z5=:z5,z6=:z6,z7=:z7,z8=:z8,
        z302=:z302,z303=:z303,z304=:z304,z305=:z305,z306=:z306,z307=:z307,z308=:z308,
        z202=:z202,z203=:z203,z204=:z204,z205=:z205,z206=:z206,z207=:z207,z208=:z208,
        z224=:z224,z225=:z225,z226=:z226,
        z242=:z242,z243=:z243,z244=:z244,z245=:z245,z246=:z246,z247=:z247,z248=:z248,
        z102=:z102,z103=:z103,z104=:z104,z105=:z105,z106=:z106,z107=:z107,z108=:z108,
        z124=:z124,z125=:z125,z126=:z126,
        z132=:z132,z133=:z133,z134=:z134,z135=:z135,z136=:z136,z137=:z137,z138=:z138,
        z102_am=:z102_am,z103_am=:z103_am,z104_am=:z104_am,z105_am=:z105_am,z106_am=:z106_am,z107_am=:z107_am,z108_am=:z108_am,
        z124_am=:z124_am
        WHERE id=:id"""

    def updateUpsRate(json) {

        def time = System.currentTimeMillis()

        sql.executeUpdate(UPS_RATE_UPDATE, [
            z2: json.z2, z3: json.z3, z4: json.z4, z5: json.z5, z6: json.z6, z7: json.z7, z8: json.z8,
            z302: json.z302, z303: json.z303, z304: json.z304, z305: json.z305, z306: json.z306, z307: json.z307, z308: json.z308,
            z202: json.z202, z203: json.z203, z204: json.z204, z205: json.z205, z206: json.z206, z207: json.z207, z208: json.z208,
            z224: json.z224, z225: json.z225, z226: json.z226,
            z242: json.z242, z243: json.z243, z244: json.z244, z245: json.z245, z246: json.z246, z247: json.z247, z248: json.z248,
            z102: json.z102, z103: json.z103, z104: json.z104, z105: json.z105, z106: json.z106, z107: json.z107, z108: json.z108,
            z124: json.z124, z125: json.z125, z126: json.z126,
            z132: json.z132, z133: json.z133, z134: json.z134, z135: json.z135, z136: json.z136, z137: json.z137, z138: json.z138,
            z102_am: json.z102_am, z103_am: json.z103_am, z104_am: json.z104_am, z105_am: json.z105_am, z106_am: json.z106_am, z107_am: json.z107_am, z108_am: json.z108_am,
            z124_am: json.z124_am,
            id: json.id ]
        )
        sql.close()

        return "{success:true,time:${System.currentTimeMillis() - time}}"
    }

    def final static UPS_ZONE =
        """SELECT SQL_CALC_FOUND_ROWS IFNULL(CAST(
        CONCAT(
        '{id:',id,
        ',zip_from:',QUOTE(zip_from),
        ',zip_to:',QUOTE(zip_to),
        ',ground:',QUOTE(IFNULL(ground,'')),
        ',_3day:',QUOTE(IFNULL(3day,'')),
        ',_2day:',QUOTE(IFNULL(2day,'')),
        ',_2day_am:',QUOTE(IFNULL(2day_am,'')),
        ',_1day_saver:',QUOTE(IFNULL(1day_saver,'')),
        ',_1day:',QUOTE(IFNULL(1day,'')),
        ',_1day_am:',QUOTE(IFNULL(1day_am,'')),
        '}') AS CHAR),'{}') json
        FROM sklad.upszone"""

    def selectUpsZone(start, limit) {

        def time = System.currentTimeMillis(),
        count, data = []

        sql.eachRow(UPS_ZONE, start, limit) {
            data << it.json
        }

        count = sql.firstRow("SELECT FOUND_ROWS() cnt").cnt
        sql.close()

        return "{success:true,total:${count},time:${System.currentTimeMillis() - time},data:[${data.join(',')}]}"
    }

    def final static UPS_ZONE_UPDATE =
        """UPDATE LOW_PRIORITY sklad.upszone SET
        ground=:ground,
        3day=:_3day,
        2day=:_2day,
        2day_am=:_2day_am,
        1day_saver=:_1day_saver,
        1day=:_1day,
        1day_am=:_1day_am
        WHERE id=:id"""

    def updateUpsZone(json) {

        def time = System.currentTimeMillis()

        sql.executeUpdate(UPS_ZONE_UPDATE, [
             ground:     json.ground,
            _3day:       json._3day,
            _2day:       json._2day,
            _2day_am:    json._2day_am,
            _1day_saver: json._1day_saver,
            _1day:       json._1day,
            _1day_am:    json._1day_am,
            id:          json.id ]
        )
        sql.close()

        return "{success:true,time:${System.currentTimeMillis() - time}}"
    }

/**********USPS***************/

    def final static USPS_RATE =
        """SELECT SQL_CALC_FOUND_ROWS IFNULL(CAST(
        CONCAT(
        '{id:',id,
        ',weight:',QUOTE(weight),
        ',z0:',IFNULL(z0,0),',z1:',IFNULL(z1,0),',z2:',IFNULL(z2,0),',z3:',IFNULL(z3,0),',z4:',IFNULL(z4,0),
        ',z5:',IFNULL(z5,0),',z6:',IFNULL(z6,0),',z7:',IFNULL(z7,0),',z8:',IFNULL(z8,0),',z9:',IFNULL(z9,0),
        ',z10:',IFNULL(z10,0),',z11:',IFNULL(z11,0),',z12:',IFNULL(z12,0),
        '}') AS CHAR),'{}') json
        FROM sklad.uspsrate"""

    def selectUspsRate(start, limit) {

        def time = System.currentTimeMillis(),
        count = 0, data = []

        sql.eachRow(USPS_RATE, start, limit) {
            data << it.json
        }

        count = sql.firstRow("SELECT FOUND_ROWS() cnt").cnt
        sql.close()

        return "{success:true,total:${count},time:${System.currentTimeMillis() - time},data:[${data.join(',')}]}"
    }

    def final static USPS_RATE_UPDATE =
        """UPDATE LOW_PRIORITY sklad.uspsrate SET
        z0=:z0,z1=:z1,z2=:z2,z3=:z3,z4=:z4,z5=:z5,z6=:z6,
        z7=:z7,z8=:z8,z9=:z9,z10=:z10,z11=:z11,z12=:z12
        WHERE id=:id"""

    def updateUspsRate(json) {

        def time = System.currentTimeMillis()

        sql.executeUpdate(USPS_RATE_UPDATE, [
            z0: json.z0, z1: json.z1, z2: json.z2, z3: json.z3, z4: json.z4, z5: json.z5, z6: json.z6,
            z7: json.z7, z8: json.z8, z9: json.z9, z10: json.z10, z11: json.z11, z12: json.z12,
            id: json.id ]
        )
        sql.close()

        return "{success:true,time:${System.currentTimeMillis() - time}}"
    }

    def final static USPS_ZONE =
        """SELECT SQL_CALC_FOUND_ROWS IFNULL(CAST(
        CONCAT(
        '{id:',id,
        ',countryID:',QUOTE(countryID),
        ',description:',QUOTE(description),
        ',zoneID:',QUOTE(zoneID),
        ',maxweight:',QUOTE(maxweight),
        '}') AS CHAR),'{}') json
        FROM sklad.uspszone"""

    def selectUspsZone(start, limit) {

        def time = System.currentTimeMillis(),
        count, data = []

        sql.eachRow(USPS_ZONE, start, limit) {
            data << it.json
        }

        count = sql.firstRow("SELECT FOUND_ROWS() cnt").cnt
        sql.close()

        return "{success:true,total:${count},time:${System.currentTimeMillis() - time},data:[${data.join(',')}]}"
    }

    def final static USPS_ZONE_UPDATE =
        """UPDATE LOW_PRIORITY sklad.uspszone SET
        countryID=:countryID,
        description=:description,
        zoneID=:zoneID,
        maxweight=:maxweight
        WHERE id=:id"""

    def updateUspsZone(json) {

        def time = System.currentTimeMillis()

        sql.executeUpdate(USPS_ZONE_UPDATE, [
            countryID:   json.countryID,
            description: json.description,
            zoneID:      json.zoneID,
            maxweight:   json.maxweight,
            id:          json.id ]
        )
        sql.close()

        return "{success:true,time:${System.currentTimeMillis() - time}}"
    }

/**********Google***************/

    def final static ITEMS_ON_GOOGLE =
        //"""SELECT SQL_CALC_FOUND_ROWS CAST(
//        IF(it.stock_quantity=0,3,it.stock_quantity) qty,iv.alias vendorName,IF(it.drone=0,0,1) drone,IFNULL(DATEDIFF(NOW(),it.stockDate),0) older,ad.upc upc,
//        IF(it.active='Y',1,0) itemActive,ig.active googleActive,ig.actionToGoogle action,ig.groups googlegroup
//        WHERE it.sell_price > 5 AND ig.send=1
        """SELECT SQL_CALC_FOUND_ROWS IFNULL(CAST(
        CONCAT(
        '{id:',it.item_id,
        ',accwareID:',QUOTE(it.accwareCatID),
        ',name:',QUOTE(IF(ig.itemname='',it.name,ig.itemname)),
        ',sellPrice:',it.sell_price,
        ',reslPrice:',it.hot_price,
        ',mfrName:',QUOTE(it.mfr_name),
        ',mfrNumber:',QUOTE(ad.mfrNumber),
        ',weight:',it.weight,
        ',cond:',QUOTE(cs.froogle),
        ',catName:',QUOTE(IF(cad.google='','Generic',cad.google)),
        '}') AS CHAR),'{}') json
        FROM sklad.items it
        LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
        LEFT JOIN sklad.itemsAdvert ad USING(item_id)
        LEFT JOIN sklad.category ct USING(category_id)
        LEFT JOIN sklad.categoryAdvert cad ON it.category_id=cad.category_id
        LEFT JOIN sklad.conditions cs ON cs.id=LEFT(RIGHT(it.accwareCatID,2),1)
        LEFT JOIN sklad.itemVendor iv USING(vendor_id)
        LEFT JOIN sklad.category gc ON ct.parent=gc.category_id
        ORDER BY """

    def getItemsOnGoogle(start, limit, sort, dir) {

        def vsort
        switch(sort) {
            case 'accwareID':
                vsort = "it.accwareCatID ${dir}"
                break
            default:
                vsort = "it.accwareCatID"
                break
        }
        return SELECT(ITEMS_ON_GOOGLE + vsort, start, limit)
    }

     def final static VENDORS_STAT =
        """SELECT count(*) cnt,
        IFNULL(CAST(
        CONCAT(
        '{vid:',it.vendor_id,
        ',alias:',QUOTE(iv.alias),
        ',name:',QUOTE(iv.name),
        ',count:',count(*),
        '}') AS CHAR),'{}') json
        FROM sklad.items it
        LEFT JOIN sklad.itemVendor iv USING(vendor_id)
        LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
        WHERE it.active='Y' AND it.sell_price > 5 AND ig.active=1
        GROUP BY it.vendor_id"""

    def getVendorsStat() {

        def time = System.currentTimeMillis(),
        total = 0, data = []

        sql.eachRow(VENDORS_STAT) {
            data << it.json
            total += it.cnt
        }

        return "{success:true,total:${total},time:${System.currentTimeMillis() - time},data:[${data.join(',')}]}"
    }

//        ',tid:',it.vendor_id,
     def final static VENDORS_ITEMS =
        """SELECT IFNULL(CAST(
        CONCAT(
        '{iid:',it.item_id,
        ',aid:',QUOTE(it.accwareCatID),
        ',cost:',it.cost_price,
        ',sell:',it.sell_price,
        ',resl:',it.hot_price,
        '}') AS CHAR),'{}') json
        FROM sklad.items it
        WHERE it.vendor_id=:venid"""

    def getVendorItems(start, limit, sort, dir, vid) {

        def time = System.currentTimeMillis(),
        count = 0, data = []
        vsort
        switch(sort) {
            case 'accwareId': vsort = 'it.accwareCatID '; break
            //case 'count': vsort = 'cnt '; break
            //case 'name':  vsort = 'iv.name '; break
            default: vsort = 'it.accwareCatID '; break
        }

//        sql.eachRow(VENDORS_ITEMS + vsort + dir, start, limit) {
        sql.eachRow(VENDORS_ITEMS,[venid: vid], start, limit) {
            data << it.json
        }
        sql.close()

        count = sql.firstRow("SELECT COUNT(*) cnt FROM sklad.items WHERE vendor_id=:venid",[venid: vid]).cnt
        sql.close()

        return "{success:true,count:${count},time:${System.currentTimeMillis() - time},data:[${data.join(',')}]}"
    }

//        ',loaded:',1,
//        ',leaf:',IF(parent=0,0,1),
//        ',parent:',parent,
//        IF(parent=0,'',',checked:0'),
//        ',cvs:1',
     def final static GET_CATEGORY =
        """SELECT IFNULL(CAST(
        CONCAT(
        '{cid:',category_id,
        ',name:',QUOTE(name),
        ',loaded:1'
        ',leaf:0',
        '}') AS CHAR),'{}') json
        FROM sklad.category
        WHERE publish='Y' AND parent=:cid"""

    def getTreeCategory(node) {

        def time = System.currentTimeMillis(),
        data = new ArrayList()

        sql.eachRow(GET_CATEGORY,[cid: node]) {
            data << it.json
        }
        sql.close()

        return "{success:true,time:${System.currentTimeMillis() - time},data:[${data.join(',')}]}"
    }


    def final static UPDATE_TREE_CATEGORY =
        """UPDATE LOW_PRIORITY sklad.uspsrate SET
        z0=:z0,z1=:z1,z2=:z2,z3=:z3,z4=:z4,z5=:z5,z6=:z6,
        z7=:z7,z8=:z8,z9=:z9,z10=:z10,z11=:z11,z12=:z12
        WHERE id=:id"""

    def updateTreeCategory(json) {

        def time = System.currentTimeMillis()

//        sql.executeUpdate(UPDATE_TREE_CATEGORY, [
//            z0: json.z0, z1: json.z1, z2: json.z2, z3: json.z3, z4: json.z4, z5: json.z5, z6: json.z6,
//            z7: json.z7, z8: json.z8, z9: json.z9, z10: json.z10, z11: json.z11, z12: json.z12,
//            id: json.id ]
//        )
//        sql.close()

        return "{success:true,time:${System.currentTimeMillis() - time}}"
    }

     def final static GET_VENDORS_CATEGORY =
        """SELECT IFNULL(CAST(CONCAT(
        '{cid:',sc.category_id,
        ',cname:',QUOTE(sc.name),
        ',vid:',it.vendor_id,
        ',vname:',QUOTE(iv.name),
        ',alias:',QUOTE(iv.alias),
        ',cnt:',count(*),
        '}')AS CHAR),'{}') json
        FROM sklad.items it
        LEFT JOIN sklad.category sc USING(category_id)
        LEFT JOIN sklad.itemVendor iv USING(vendor_id)
        WHERE it.active='Y' AND sc.publish='Y' AND sc.parent=:cid
        GROUP BY it.vendor_id,sc.category_id"""

//        FROM advert.advertSend ads
    def final static GET_VENDORS_ACTIVE =
        """SELECT IFNULL(CAST(CONCAT(
        '{gid:',ads.advert_id,
        ',vid:',ads.vendor_id,
        ',cid:',ads.subcat_id,
        '}')AS CHAR),'{}') json
        FROM advert.advertSend ads
        LEFT JOIN sklad.advert aad USING(advert_id)
        WHERE subcat_id IN(
            SELECT category_id FROM sklad.category
            WHERE publish='Y' AND parent=:cid
        ) AND ads.advert_id=18""" // 18 == froogle

    def getVendorsCategory(cid) {

        def time = System.currentTimeMillis(),
        data = [], group = new ArrayList()

        sql.eachRow(GET_VENDORS_CATEGORY,[cid: cid]) {
            data << it.json
        }

        sql.eachRow(GET_VENDORS_ACTIVE,[cid: cid]) {
            group << it.json
        }
        sql.close()

        return "{success:true,time:${System.currentTimeMillis() - time},data:[${data.join(',')}],group:[${group.join(',')}]}"
    }

    def final static UPDATE_ADVERT_CATEGORY =
        """UPDATE LOW_PRIORITY sklad.uspsrate SET
        z0=:z0,z1=:z1,z2=:z2,z3=:z3,z4=:z4,z5=:z5,z6=:z6,
        z7=:z7,z8=:z8,z9=:z9,z10=:z10,z11=:z11,z12=:z12
        WHERE id=:id"""

    def updateVendorsCategory(json) {

        def time = System.currentTimeMillis(),
            cid, alias, vid, active = false, aid = 18

        json.data.each{ key, val ->
            if(key == 'cid') {
                cid = val
            }
            else {
                alias = key
                active = val
            }
        }

        vid = sql.firstRow("SELECT vendor_id vid FROM sklad.itemVendor WHERE alias=:als", [als: alias]).vid

        if(active) {
            sql.executeUpdate("INSERT LOW_PRIORITY advert.advertSend SET advert_id=:aid,vendor_id=:vid,subcat_id=:cid", [aid:aid,vid:vid,cid:cid])
        }
        else {
            sql.executeUpdate("DELETE LOW_PRIORITY FROM advert.advertSend WHERE advert_id=:aid AND vendor_id=:vid AND subcat_id=:cid", [aid:aid,vid:vid,cid:cid])
        }
        sql.close()

        return "{success:true,action:${active ? '\'insert\'':'\'delete\''},time:${System.currentTimeMillis() - time}}"
    }
















    def private SELECT(select, start, limit) {

        def time = System.currentTimeMillis(),
        count = 0, data = new ArrayList()

        sql.eachRow(select, start, limit) {
            data << it.json
        }
        count = sql.firstRow("SELECT FOUND_ROWS() cnt").cnt
        sql.close()

        return "{success:true,total:${count},time:${System.currentTimeMillis() - time},data:[${data.join(',')}]}"
    }


}

